﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Collections;
namespace mealplanner.database
{

    public static class MealDB
    {
        public static string comm;
        /// <summary>
        /// get  meal name and type from database
        /// </summary>
        /// <returns>object from mysqldatareader </returns>

        public static ArrayList getAllMealNameAndType()
        {
            ArrayList list = new ArrayList();
            comm = "select meal.meal_name,meal.type from meal";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["meal_name"]);
                list.Add(reader["type"]);
            }

            return list;
        }


        /// <summary>
        /// get meal information { ingredient_name , quantity of ingredient ,cost and calories }by name of meal
        /// </summary>
        /// <param name="id"> meal_id </param>
        /// <returns> return all of this </returns>

        public static ArrayList getMealCostName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "select mealplanner.ingredient.cost,mealplanner.meal_ingredient.quantity from mealplanner.meal,mealplanner.ingredient,mealplanner.meal_ingredient where mealplanner.meal.meal_id = mealplanner.meal_ingredient.meal_id and mealplanner.ingredient.ingredient_id = mealplanner.meal_ingredient.ingredient_id and mealplanner.meal.meal_name =\'" + name + "\'";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["cost"]);
                list.Add(reader["quantity"]);
            }
            Database.con.Close();
            return list;
        }

        public static ArrayList getMealIngredienByMealName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "select mealplanner.ingredient.ingredient_name from mealplanner.meal,mealplanner.ingredient,mealplanner.meal_ingredient where mealplanner.meal.meal_id = mealplanner.meal_ingredient.meal_id and mealplanner.ingredient.ingredient_id = mealplanner.meal_ingredient.ingredient_id and mealplanner.meal.meal_name =\'" + name + "\'";
            MySqlDataReader reader = Database.executeCommand(comm);

            while (reader.Read())
            {
                list.Add(reader["ingredient_name"]);

            }
            Database.con.Close();
            return list;
        }

        /// <summary>
        /// get meal description by meal name .
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>

        public static ArrayList getDescriptionByMealName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "select meal.description from meal where meal_name =\'" + name + "\'";
            //comm = "select mealplanner.ingredient.cost,mealplanner.ingredient.calories from mealplanner.ingredient where ingredient.ingredient_id = " + id;
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["description"]);
            }
            Database.con.Close();
            return list;
        }

        /// <summary>
        /// get meal image by meal name
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>

        public static ArrayList getImageByMealName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "select meal.image from meal where meal_name =\'" + name + "\'";
            //comm = "select mealplanner.ingredient.cost,mealplanner.ingredient.calories from mealplanner.ingredient where ingredient.ingredient_id = " + id;
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["image"]);

            }
            Database.con.Close();
            return list;

        }


        /// <summary>
        /// get meal information { ingredient_name , quantity of ingredient ,cost and calories }by name of meal
        /// </summary>
        /// <param name="id"> meal_id </param>
        /// <returns> return all of this </returns>

        public static ArrayList getMealIngredientByMealName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "select mealplanner.ingredient.ingredient_name,mealplanner.ingredient.cost,mealplanner.ingredient.calories,mealplanner.meal_ingredient.quantity from mealplanner.meal,mealplanner.ingredient,mealplanner.meal_ingredient where mealplanner.meal.meal_id = mealplanner.meal_ingredient.meal_id and mealplanner.ingredient.ingredient_id = mealplanner.meal_ingredient.ingredient_id and mealplanner.meal.meal_name =\'" + name + "\'";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["ingredient_name"]);
                list.Add(reader["cost"]);
                list.Add(reader["calories"]);
                list.Add(reader["quantity"]);

            }
            Database.con.Close();
            return list;
        }

        /// <summary>
        /// search for meal with meal name 
        /// </summary>
        /// <param name="name"> the meal name </param>
        /// <returns> all meal similar to the search </returns>
        public static ArrayList searchMealByMealName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "SELECT mealplanner.meal.meal_name FROM mealplanner.meal WHERE mealplanner.meal.meal_name LIKE \'%" + name + "%\'";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["meal_name"]);

            }
            Database.con.Close();
            return list;
        }

        /// <summary>
        /// method to get meal id  by meal name
        /// </summary>
        /// <param name="name"> meal name </param>
        /// <returns></returns>
        public static ArrayList getMealIdByMealName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "select meal_id from mealplanner.meal where meal_name =\'" + name + "\'";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["meal_id"]);
            }
            Database.con.Close();
            return list;
        }

    }
}
